package com.zsr.blob;

import com.zsr.utils.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;

/**
 * 批量插入测试
 */
public class InsertTest {

    /**
     * 实现层次一：使用Statement
     */
    @Test
    public void test1() {
        try {
//            Connection conn = JDBCUtils.getConn();
//            Statement st = conn.createStatement();
//            for(int i = 1;i <= 20000;i++){
//                String sql = "insert into goods(name) values('name_' + "+ i +")";
//                st.executeUpdate(sql);
//            }
//
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 实现层次二：使用PreparedStatement
     */
    @Test
    public void test2() {
        try {
            long start = System.currentTimeMillis();

            Connection conn = JDBCUtils.getConn();
            String sql = "insert into goods(name) values(?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            for (int i = 1; i <= 20000; i++) {
                ps.setObject(1, "name_" + i);
                ps.executeUpdate();
            }

            long end = System.currentTimeMillis();
            System.out.println("花费的时间为：" + (end - start));//花费的时间为：12117
            JDBCUtils.closeResource(conn, ps);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 实现层次三
     * 修改1： 使用 addBatch() / executeBatch() / clearBatch()
     * 修改2：mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。
     * ?rewriteBatchedStatements=true 写在配置文件的url后面
     * 修改3：使用更新的mysql 驱动：mysql-connector-java-5.1.37-bin.jar
     */
    @Test
    public void test3() {
        try {
            long start = System.currentTimeMillis();

            Connection conn = JDBCUtils.getConn();
            String sql = "insert into goods(name) values (?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            for (int i = 1; i <= 20000; i++) {
                ps.setObject(1, "name_" + i);
                //1.“攒”sql
                ps.addBatch();

                if (i % 500 == 0) {
                    //2.执行
                    ps.executeBatch();
                    //3.清空
                    ps.clearBatch();
                }
            }
            long end = System.currentTimeMillis();
            System.out.println("花费的时间为：" + (end - start));//20000条：625
//1000000条:14733

            JDBCUtils.closeResource(conn, ps);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 层次四：在层次三的基础上操作
     * 使用Connection 的 setAutoCommit(false) / commit()
     */
    @Test
    public void test4() {
        try {
            long start = System.currentTimeMillis();
            Connection conn = JDBCUtils.getConn();
            String sql = "insert into goods(name) values (?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            conn.setAutoCommit(false);
            for (int i = 1; i <= 20000; i++) {
                ps.setObject(1, "name_" + i);

                ps.addBatch();
                if (i % 500 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            //2.提交数据
            conn.commit();

            long end = System.currentTimeMillis();
            System.out.println("花费的时间为：" + (end - start));//花费的时间为：1036

            JDBCUtils.closeResource(conn, ps);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

}
